curl -L -o ToeiBus-GTFS.zip https://api-public.odpt.org/api/v4/files/Toei/data/ToeiBus-GTFS.zip

unzip ToeiBus-GTFS.zip

# ls -1
ToeiBus-GTFS.zip
agency.txt: 運行会社情報
agency_jp.txt
attributions.txt
calendar.txt: 運行日情報
calendar_dates.txt: 運行日付情報
fare_attributes.txt: 運賃情報
fare_rules.txt: 運賃ルール情報
feed_info.txt: フィード情報
office_jp.txt
routes.txt: 路線情報
shapes.txt: 路線形状情報
stop_times.txt: 時刻表情報
stops.txt: 停留所情報
translations.txt
trips.txt: 便情報

$ duckdb ViewGTFS.duckdb

v1.0.0 1f98600c2c
Enter ".help" for usage hints.
D 

INSTALL spatial;
LOAD spatial;

-- stops.csvをstopsテーブルに保存する
CREATE OR REPLACE TABLE stops AS SELECT * FROM read_csv('stops.txt');
CREATE OR REPLACE TABLE shapes AS SELECT * FROM read_csv('shapes.txt');
CREATE OR REPLACE TABLE trips AS SELECT * FROM read_csv('trips.txt');

SELECT * FROM stops LIMIT 5;

CREATE OR REPLACE TABLE stop_times AS SELECT * FROM read_csv(
    'stop_times.txt', 
    header=true, 
    columns={ 
        'trip_id': 'VARCHAR', 
        'arrival_time': 'VARCHAR', 
        'departure_time': 'VARCHAR', 
        'stop_id': 'VARCHAR', 
        'stop_sequence': 'INTEGER', 
        'stop_headsign': 'VARCHAR', 
        'pickup_type': 'UTINYINT', 
        'drop_off_type': 'UTINYINT', 
        'shape_dist_traveled': 'DOUBLE', 
        'timepoint': 'UTINYINT' 
});

# Usage: bash GTFS2DuckDB.sh DB_NAME GTFS_URL GTFS_TITLE
#bash ./GTFS2DuckDB.bash gtfs https://api-public.odpt.org/api/v4/files/Toei/data/ToeiBus-GTFS.zip toeibus
#bash ./GTFS2DuckDB.bash gtfs https://api-public.odpt.org/api/v4/files/JR-Freight/data/JR-Freight-Train-GTFS-250.zip jrf

DBNAME=$1
GTFS_URL=$2
TITLE=$3
GTFS_FILE=${GTFS_URL##*/}

echo "Download: $GTFS_FILE"
curl -L -o $TITLE.zip $GTFS_URL
echo "Import: $GTFS_FILE to $TITLE.duckdb"
duckdb $DBNAME.duckdb -c "CREATE SCHEMA IF NOT EXISTS $TITLE"

for basename in `unzip -Z1 $TITLE.zip`; do
    filename=${basename%.*}
    echo "${filename} to DuckDB"
    # stop_timesのみスキーマ自動判定が失敗するので、手動でスキーマ定義を渡す（arrival_time, departure_timeが25:00:00のような24時越えの値を含むため）
    if [ $filename = "stop_times" ]; then
        unzip -p $TITLE.zip $basename | duckdb $DBNAME.duckdb -c "CREATE OR REPLACE TABLE $TITLE.${filename} AS SELECT * FROM read_csv('/dev/stdin', header=true, columns={ 'trip_id': 'VARCHAR', 'arrival_time': 'VARCHAR', 'departure_time': 'VARCHAR', 'stop_id': 'VARCHAR', 'stop_sequence': 'INTEGER', 'stop_headsign': 'VARCHAR', 'pickup_type': 'UTINYINT', 'drop_off_type': 'UTINYINT', 'shape_dist_traveled': 'DOUBLE', 'timepoint': 'UTINYINT' })"
    else
        unzip -p $TITLE.zip $basename | duckdb $DBNAME.duckdb -c "CREATE OR REPLACE TABLE $TITLE.${filename} AS SELECT * FROM read_csv('/dev/stdin')"
    fi
done

import duckdb
import pandas as pd
from google.transit import gtfs_realtime_pb2
from google.protobuf import json_format
import requests

# %% GTFS-RTを取得してパースする
feed = gtfs_realtime_pb2.FeedMessage()
response = requests.get('https://api-public.odpt.org/api/v4/gtfs/realtime/ToeiBus')
feed.ParseFromString(response.content)
# %% パースしたデータをPython Dictにした上でDataFrameに変換
f = json_format.MessageToDict(feed)
# pd.json_normalizeでentityに含まれるデータのKeyをFlattingした上でDataFrameに変換
df_feed = pd.json_normalize(f["entity"])
# vehicle.timestampをUnixTimeからDateTimeに変換、この際UTCとして扱うようにしておくことで日本時間で扱いやすくなる
df_feed['vehicle.timestamp'] = pd.to_datetime(df_feed['vehicle.timestamp'], unit='s', utc=True)
# %% DuckDBに保存（MotherDuckに保存する場合はmd:gtfsrt?motherduck_token=MD_TOKEN）
con = duckdb.connect("gtfsrt.duckdb")
# 初回だけこちらを実行してください
con.execute("CREATE SCHEMA IF NOT EXISTS toeibus")
con.execute("CREATE TABLE IF NOT EXISTS toeibus.vehicles AS SELECT * FROM df_feed")
# 2回目以降はこちら
con.execute("INSERT INTO toeibus.vehicles SELECT * FROM df_feed")
con.close()

-- 出力するデータのスキーマを選択
USE toeibus;

-- stopsをCSVで出力する
COPY (
    SELECT * FROM stops
) TO 'stops.csv' WITH (FORMAT CSV, HEADER);

-- TripsをShapesから得られたLineStringのGeoJSONで出力する
COPY (
    SELECT * FROM trips
    LEFT JOIN (
        SELECT 
            shape_id,
            {
                "type": 'LineString',
                "coordinates": list([shape_pt_lon,shape_pt_lat] ORDER BY shape_pt_sequence ASC)
            }::JSON AS "geometry" 
        FROM shapes GROUP BY shape_id
    ) AS shapes ON trips.shape_id = shapes.shape_id
) TO 'trips.csv' WITH (FORMAT CSV, HEADER);

COPY (
    SELECT 'FeatureCollection' AS "type", LIST(feature) AS "features" FROM (
    SELECT 
    {
    "type": 'Feature',
    "properties": {"id": id},
    "geometry": {
        "type": 'LineString',
        "coordinates": array_agg(["vehicle.position.longitude", "vehicle.position.latitude", 0, epoch("vehicle.timestamp")] ORDER BY "vehicle.timestamp" ASC)
        } 
    -- WHERE句は必要に応じて調整してください
    }::JSON AS feature FROM toeibus.vehicles WHERE "vehicle.timestamp" > '2024-12-13 08:00:00' GROUP BY id)
) TO 'trip.json' (FORMAT JSON);

-- Shapesを元に運行頻度を出してみる
USE gtfs.jrf;
COPY (
    WITH shape_segments AS (
        SELECT 
            s1.shape_id,
            s1.shape_pt_sequence AS from_seq,
            s1.shape_pt_lon AS from_lon,
            s1.shape_pt_lat AS from_lat,
            s2.shape_pt_sequence AS from_seq2,
            s2.shape_pt_lon AS to_lon,
            s2.shape_pt_lat AS to_lat
        FROM 
            shapes s1
        JOIN 
            shapes s2 ON s1.shape_id = s2.shape_id AND s1.shape_pt_sequence = s2.shape_pt_sequence - 1
    )
    SELECT count(shape_id), from_lon, from_lat, to_lon, to_lat
    FROM shape_segments
    GROUP BY from_lon, from_lat, to_lon, to_lat
) TO 'frequency.csv' WITH (FORMAT CSV, HEADER);

INSTALL h3 FROM community;
LOAD h3;
INSTALL spatial;
load spatial;

CREATE TABLE land_price AS SELECT * FROM ST_Read('L01-24.geojson');
COPY (SELECT L01_008, h3_latlng_to_cell_string(ST_Y(geom), ST_X(geom), 9) FROM land_price) TO 'land_price.csv' (HEADER, DELIMITER ',');

-- gtfs.duckdb を'gtfs'としてアタッチ
ATTACH 'gtfs.duckdb' AS gtfs (READ_ONLY);

COPY (
    SELECT h3_latlng_to_cell_string(stop_lat, stop_lon, 9), stop_name, lp.price FROM gtfs.toeibus.stops
    -- 都バスGTFSのstopsと地価公示データのPointをそれぞれH3に変換して公示価格をstopsに対してJOIN
    LEFT JOIN (SELECT L01_008 AS price, h3_latlng_to_cell_string(ST_Y(geom), ST_X(geom), 9) AS h3 FROM land_price) AS lp
    ON h3_latlng_to_cell_string(stop_lat, stop_lon, 9) = lp.h3
    ORDER BY lp.price DESC
) TO 'pole_price.csv' (HEADER, DELIMITER ',');

$ unzip QDDSWQ5339.zip
$ duckdb ViewGTFS.duckdb
v1.0.0 1f98600c2c
Enter ".help" for usage hints.
D load spatial;
D CREATE OR REPLACE TABLE mesh_5th AS SELECT * FROM ST_Read('MESH05339.shp');

$ unzip -p tblT001142Q5339.zip | iconv -c -f cp932 -t utf-8 > tblT001142Q5339_utf8.txt

$ duckdb ViewGTFS.duckdb
v1.0.0 1f98600c2c
Enter ".help" for usage hints.
D load spatial;
-- 2行の日本語ヘッダーがレコードとして含まれているが気にしない
D CREATE OR REPLACE TABLE census AS SELECT * FROM read_csv('tblT001142Q5339_utf8.txt', header = true);

-- メッシュ内総人口はT001142001カラムに保存されているので、メッシュコードのポリゴンと人口をJOINしてGeoJSONとして保存する
D COPY (
    SELECT mesh_5th.KEY_CODE, census.T001142001 AS population, geom FROM mesh_5th 
    LEFT JOIN census ON mesh_5th.KEY_CODE = census.KEY_CODE
) TO 'census.geojson' WITH (FORMAT GDAL, DRIVER 'GeoJSON', LAYER_CREATION_OPTIONS 'WRITE_BBOX=YES');